package com.neusoft.hifly.dvpt.util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.neusoft.hifly.commons.file.EasyPoiUtil;
import com.neusoft.hifly.commons.file.FileUtil;
import com.neusoft.hifly.commons.lang.StringConstants;
import com.neusoft.hifly.core.log.Log;
import com.neusoft.hifly.dvpt.domain.MenuVO;

public class CreateSQLUtil {

	public void createSql(final String filePath) {
		final List<MenuVO> menus = readExcel(filePath);
		int menuCount = 10;
		int itemCount = 10;
		String appId = "";
		String item1Id = "";
		String item2Id = "";
		String item3Id = "";
		final Map<String, List<String>> appIdMap = new HashMap<String, List<String>>();
		for (final MenuVO map : menus) {

			if (!StringConstants.NO_STR.equals(map.getStatus())) {
				map.setStatus(StringConstants.YES);
			}

			if (StringUtils.isNotEmpty(StringUtils.trimToEmpty(map.getAppName()))) {
				menuCount = menuCount + 10;
				appId = (90000 - menuCount) + "";

				appIdMap.put(appId, new ArrayList<String>());

				System.out.println("-- 应用");
				System.out.println("DELETE FROM CBUS_AUC_APP_INFO WHERE ID = '" + appId + "';");
				System.out.println("DELETE FROM CBUS_AUC_PERMISSION_INFO WHERE APP_ID = '" + appId + "';");
				System.out.println(
						"INSERT INTO CBUS_AUC_APP_INFO (ID, APP_CODE, APP_NAME, APP_REMARK, IS_ONLINE, APP_ORDER) VALUES ('"
								+ appId + "', '" + StringUtils.trimToEmpty(map.getAppCode()) + "', '"
								+ StringUtils.trimToEmpty(map.getAppName()) + "', '"
								+ StringUtils.trimToEmpty(map.getMemo()) + "', '" + map.getStatus() + "', " + menuCount
								+ ");");

				System.out.println("DELETE FROM CBUS_AUC_ROLE_PERMISSION WHERE ROLE_ID = '" + appId + "';");

				System.out.println("-- 用户");
				System.out.println("DELETE FROM CBUS_AUC_USER_INFO WHERE ID = '" + appId + "';");
				System.out.println(
						"INSERT INTO CBUS_AUC_USER_INFO (ID, USER_NAME, LOGIN_NAME, LOGIN_PASSWORD, USER_CODE, IS_LOCK, IS_DELETE) VALUES ('"
								+ appId + "', '" + StringUtils.trimToEmpty(map.getAppName()) + "用户', '"
								+ StringUtils.trimToEmpty(map.getAppCode()) + "', 'e10adc3949ba59abbe56e057f20f883e', '"
								+ StringUtils.trimToEmpty(map.getAppCode()) + "', '1', '0');");

				System.out.println("DELETE FROM CBUS_AUC_USER_APP WHERE APP_ID = '" + appId + "';");
				System.out.println(
						"INSERT INTO CBUS_AUC_USER_APP (USER_ID, APP_ID) VALUES ('" + appId + "', '" + appId + "');");

				System.out.println("-- 角色");
				System.out.println("DELETE FROM CBUS_AUC_ROLE_INFO WHERE APP_ID = '" + appId + "';");

				System.out.println("INSERT INTO CBUS_AUC_ROLE_INFO (ID, ROLE_NAME, APP_ID, IS_ENABLE) VALUES ('" + appId
						+ "', '" + map.getAppName() + "管理员', '" + appId + "', 1);");

				System.out.println("DELETE FROM CBUS_AUC_USER_ROLE WHERE ROLE_ID = '" + appId + "';");
				System.out.println(
						"INSERT INTO CBUS_AUC_USER_ROLE (USER_ID, ROLE_ID) VALUES ('" + appId + "', '" + appId + "');");

				System.out.println("-- 菜单");
			}

			if (StringUtils.isNotEmpty(map.getName1())) {
				itemCount = itemCount + 10;
				item1Id = (90000 - itemCount) + "";
				System.out.println("INSERT INTO CBUS_AUC_PERMISSION_INFO "
						+ "(ID, APP_ID, PARENT_ID, PERMISSION_CODE, PERMISSION_NAME, PERMISSION_URL, PERMISSION_REMARK, PERMISSION_ORDER) "
						+ "VALUES ('" + item1Id + "', '" + appId + "', '0', '" + StringUtils.trimToEmpty(map.getAlias())
						+ "', '" + map.getName1() + "', '" + StringUtils.trimToEmpty(map.getPath()) + "', '"
						+ StringUtils.trimToEmpty(map.getMemo()) + "', " + itemCount + ");");
				appIdMap.get(appId).add(item1Id);
			} else if (StringUtils.isNotEmpty(map.getName2())) {
				itemCount = itemCount + 10;
				item2Id = (90000 - itemCount) + "";

				System.out.println("INSERT INTO CBUS_AUC_PERMISSION_INFO "
						+ "(ID, APP_ID, PARENT_ID, PERMISSION_CODE, PERMISSION_NAME, PERMISSION_URL, PERMISSION_REMARK, PERMISSION_ORDER) "
						+ "VALUES ('" + item2Id + "', '" + appId + "', '" + item1Id + "', '"
						+ StringUtils.trimToEmpty(map.getAlias()) + "', '" + map.getName2() + "', '"
						+ StringUtils.trimToEmpty(map.getPath()) + "', '" + StringUtils.trimToEmpty(map.getMemo())
						+ "', " + itemCount + ");");
				appIdMap.get(appId).add(item2Id);
			} else if (StringUtils.isNotEmpty(map.getName3())) {
				itemCount = itemCount + 10;
				item3Id = (90000 - itemCount) + "";
				System.out.println("INSERT INTO CBUS_AUC_PERMISSION_INFO "
						+ "(ID, APP_ID, PARENT_ID, PERMISSION_CODE, PERMISSION_NAME, PERMISSION_URL, PERMISSION_REMARK, PERMISSION_ORDER) "
						+ "VALUES ('" + item3Id + "', '" + appId + "', '" + item2Id + "', '"
						+ StringUtils.trimToEmpty(map.getAlias()) + "', '" + map.getName3() + "', '"
						+ StringUtils.trimToEmpty(map.getPath()) + "', '" + StringUtils.trimToEmpty(map.getMemo())
						+ "', " + itemCount + ");");
				appIdMap.get(appId).add(item3Id);
			}
		}

		System.out.println("-- 权限");
		Iterator<Map.Entry<String, List<String>>> iter = appIdMap.entrySet().iterator();
		while (iter.hasNext()) {
			final Map.Entry<String, List<String>> entry = iter.next();
			final String id = entry.getKey();
			final List<String> itemIds = entry.getValue();
			itemIds.forEach(itemId -> {
				System.out.println("INSERT INTO CBUS_AUC_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID) VALUES ('" + id
						+ "', '" + itemId + "');");
			});
		}

	}

	public List<MenuVO> readExcel(final String path) {
		if (!FileUtil.exists(path)) {
			Log.error("导入失败!未找到Excel文件" + path);
		}

		final List<MenuVO> result = EasyPoiUtil.importExcel(path, MenuVO.class);
		//		System.out.println(JSON.toJSONString(result));

		return result;
	}
}
